package com.tnl.app.dao;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.tnl.app.entity.Hunter;
import com.tnl.base.BaseDAO;

@Repository
public class HunterDAO {

	static Logger logger = LoggerFactory.getLogger(HunterDAO.class);

	protected static final String SQL_SAVE = "insert into tb_hunter(openid,name,englishname,idnumber, age,cityId, workage,"
			+ "phone,advantage,email,binded,"
			+ "majordomain1, majordomain2, majordomain3,active,createDate,companyId, companyname, comment, "
			+ "headimgurl,secureCode,bestDomain) "
			+ "values(?,?,?,?"
			+ ",?,?,?,?" + ",?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	protected static final String SQL_SAVE_SECURECODE = "insert into tb_hunter(openid,"
			+ "phone, secureCode,expiredTime,state,binded,active ) "
			+ "values(?,?,?,?,?,?,?)";
	protected static final String SQL_UPDATE_PHONE = "update tb_hunter set openid=?, "
			+ "phone=?,state=? where id =?";

	private static final String SQL_DELETE = "delete from tb_hunter where id=?";

	private static final String SQL_GET_ID = "select * from tb_hunter where id = ? ";

	private static final String SQL_GET_OPENID = "select * from tb_hunter where openid = ? ";
	private static final String SQL_GET_OPENID_STATUS = "select * from tb_hunter where openid = ? and active =?";
	private static final String SQL_GET_ALL = "select * from tb_hunter";

	private static final String SQL_GET_COMPANY = "select * from  tb_hunter "
			+ " where binded = 1 and companyId = ?";

	private static final String SQL_GET_COMPANY_COUNT = "select COUNT(*) from  tb_hunter "
			+ " where binded = 1 and companyId = ?";

	protected static final String SQL_GET_BY_PHONE_OPENID = "select * from tb_hunter  where phone = ? and openId = ?";
	private static final String SQL_GET_HUNTER_COUNT = "SELECT COUNT(*) FROM tb_hunter where 1=1 ";

	private static final String SQL_GET_HUNTER_WITH_LIMIT = "SELECT a.* FROM tb_hunter a  ";

	public int save(Hunter hunter) {
		if (hunter == null) {
			return -1;
		}
		Long id = BaseDAO.save(SQL_SAVE, hunter.getOpenid(), hunter.getName(),
				hunter.getEnglishname(), hunter.getIdnumber(), hunter.getAge(),
				hunter.getCityId(), hunter.getWorkage(), hunter.getPhone(),
				hunter.getAdvantage(), hunter.getEmail(), hunter.getBinded(),
				hunter.getMajordomain1(), hunter.getMajordomain2(),
				hunter.getMajordomain3(), hunter.getActive(),
				hunter.getCreateDate(), hunter.getCompanyId(),
				hunter.getCompanyName(), hunter.getComment(),
				hunter.getHeadImgUrl(), hunter.getSecureCode(),
				hunter.getBestdomain());
		if (id == null) {
			return -1;
		}

		return id.intValue();
	}

	public int saveSecureCode(Hunter hunter) {
		if (hunter == null) {
			return -1;
		}
		Long id = BaseDAO.save(SQL_SAVE_SECURECODE, hunter.getOpenid(),
				hunter.getPhone(), hunter.getSecureCode(),
				hunter.getExpiredTime(), hunter.getState().toString(), 0, 1);
		if (id == null) {
			return -1;
		}

		return id.intValue();
	}

	public int delete(String Id) {

		if (Id != null) {

			int result = BaseDAO.execute(SQL_DELETE, Id);
			return result;
		} else {
			return 0;
		}
	}

	public List<Hunter> getALL() {
		return BaseDAO.queryForList(Hunter.class, SQL_GET_ALL);
	}

	protected static final String SQL_UPDATE = "update tb_hunter "
			+ "set openid=?, name=?, englishname=?, "
			+ "idnumber =? , cityId=?,  workage=?,"
			+ " advantage =?, email=?, "
			+ "majordomain1=?, majordomain2=?, majordomain3=? ,"
			+ " active=?,  comment=?, headImgUrl =?  where id =?";

	public int update(Hunter hunter) {
		return BaseDAO.execute(SQL_UPDATE, hunter.getOpenid(),
				hunter.getName(), hunter.getEnglishname(),
				hunter.getIdnumber(), hunter.getCityId(), hunter.getWorkage(),
				hunter.getAdvantage(), hunter.getEmail(),
				hunter.getMajordomain1(), hunter.getMajordomain2(),
				hunter.getMajordomain3(), hunter.getActive(),
				hunter.getComment(), hunter.getImageUrl(), hunter.getId());
	}

	protected static final String SQL_UPDATE_PROFILE = "update tb_hunter "
			+ " set openid=?, name=?, englishname=?, idnumber=?, "
			+ " cityId=?, cityName=?, workage=?, advantage=?, "
			+ "email=?, majordomain1=?, domainName1=? ,   majordomain2=? , "
			+ "domainName2=?,  majordomain3=? , domainName3=?  , createDate=?,  "
			+ "state=?, comment=?, headImgUrl =? , "
			+ " bestDomain=? ,serviceTags=? where id =?";

	public int updateProfile(Hunter hunter) {
		return BaseDAO
				.execute(SQL_UPDATE_PROFILE, hunter.getOpenid(),
						hunter.getName(), hunter.getEnglishname(),
						hunter.getIdnumber(), hunter.getCityId(),
						hunter.getCityName(), hunter.getWorkage(),
						hunter.getAdvantage(),

						hunter.getEmail(), hunter.getMajordomain1(),
						hunter.getDomainName1(), hunter.getMajordomain2(),
						hunter.getDomainName2(), hunter.getMajordomain3(),
						hunter.getDomainName3(), hunter.getCreateDate(),
						hunter.getState(), hunter.getComment(),
						hunter.getHeadImgUrl(), hunter.getBestdomain(),
						hunter.getServiceTags(), hunter.getId());
	}

	public int bindedPhone(Hunter hunter) {
		return BaseDAO
				.execute(SQL_UPDATE_PHONE, hunter.getOpenid(),
						hunter.getPhone(), hunter.getState().toString(),
						hunter.getId());
	}

	public Hunter getById(Integer id) {
		return BaseDAO.queryForObject(Hunter.class, SQL_GET_ID, id);
	}

	public Hunter getByActivateHunter(String openid) {
		return getByOpenId(openid, Hunter.activate);
	}

	public Hunter getByOpenId(String openid, int status) {

		return BaseDAO.queryForObject(Hunter.class, SQL_GET_OPENID_STATUS,
				openid, status);
	}

	public Hunter getByOpenId(String openid) {

		return BaseDAO.queryForObject(Hunter.class, SQL_GET_OPENID, openid);
	}

	public List<Hunter> getBindedByCompanyId(int companyId) {
		return BaseDAO.queryForList(Hunter.class, SQL_GET_COMPANY, companyId);
	}

	public Long getBindedByCompanyIdCount(int companyId) {
		return (Long) BaseDAO.queryOneRowOneCol(SQL_GET_COMPANY_COUNT,
				companyId);
	}

	protected static final String SQL_GET_BY_PHONE = "select * from tb_hunter  where phone = ? ";

	public Hunter getByPhoneandOpenId(String phone, String openId) {
		return BaseDAO.queryForObject(Hunter.class, SQL_GET_BY_PHONE_OPENID,
				phone, openId);
	}

	public Hunter getByPhone(String phone) {
		return BaseDAO.queryForObject(Hunter.class, SQL_GET_BY_PHONE, phone);
	}

	public Long getCountOfAll(String name, Integer companyId, String phoneNum) {
		String sql = SQL_GET_HUNTER_COUNT;
		if (StringUtils.hasText(name)) {
			sql += " and name like '" + name + "'";
		}
		if (companyId != null) {
			sql += " and companyId =" + companyId + " ";
		}
		if (StringUtils.hasText(phoneNum)) {
			sql += " and phone = '" + phoneNum + "' ";
		}
		return (Long) BaseDAO.queryOneRowOneCol(sql);
	}

	public List<Hunter> getAllWithLimit(String name, Integer companyId,
			String phoneNum, long start, long length) {
		String sql = SQL_GET_HUNTER_WITH_LIMIT;
		if (StringUtils.hasText(name)) {
			sql += " and a.name like '" + name + "'";
		}
		if (companyId != null) {
			sql += " and companyId =" + companyId + " ";
		}
		if (StringUtils.hasText(phoneNum)) {
			sql += " and a.phone = '" + phoneNum + "' ";
		}
		sql += " LIMIT ?, ?";
		return BaseDAO.queryForList(Hunter.class, sql, start, length);
	}

	private static final String SQL_GET_BY_BESTDOMAIN_PART1 = "select * from  tb_hunter  "
			+ " where binded = 1 ";

	private static final String SQL_GET_BY_BESTDOMAIN = "select * from  tb_hunter "
			+ " where binded = 1 and (majordomain1 in (?,?,?) or majordomain2 in (?,?,?)"
			+ "or  majordomain3 in(?,?,?)) LIMIT ?, ? ";

	public List<Hunter> getbyBestDomain(int offset, int... domainIds) {
		StringBuffer buffer = new StringBuffer();
		String str = "";
		if (domainIds != null && domainIds.length > 0) {
			buffer.append("( ");
			for (int i : domainIds) {
				buffer.append(i).append(",");

			}
			str = buffer.substring(0, buffer.length() - 1);
			str += " )";
		}
		String sql = SQL_GET_BY_BESTDOMAIN_PART1 + "and (majordomain1 in "
				+ str + " or majordomain2 in " + str + " or majordomain3 in "
				+ str + " ) ";
		sql += " LIMIT ?, ?";
		logger.debug("get hunter by domain sql is "
				+ SQL_GET_BY_BESTDOMAIN_PART1);

		return BaseDAO.queryForList(Hunter.class, sql, offset, 10);
	}

	public Hunter getByEmail(String emailAddr, String openId) {
		String sql = "select * from tb_hunter where email like ? and openId like ?";
		return BaseDAO.queryForObject(Hunter.class, sql, emailAddr, openId);

	}

	public int deactivate(Hunter hunter) {
		return BaseDAO.execute(
				"update tb_hunter set active =? where openid =? ",
				hunter.getActive(), hunter.getOpenid());
	}

	public void activate(Hunter hunter) {
		BaseDAO.execute("update tb_hunter set active =? where openid =? ",
				hunter.getActive(), hunter.getOpenid());
	}

	public void updateImage(Hunter hunter) {
		BaseDAO.execute("update tb_hunter set imageUrl =? where openid =? ",
				hunter.getImageUrl(), hunter.getOpenid());
	}
	private static final String SQL_GET_LIST= "select a.* from  tb_hunter a "
			+ " where EXISTS(select * from tb_hunter_desc b where a.id=b.hunterId and a.binded=1) order by sort asc limit ?,?";
	
	private static final String SQL_GET_LIST_COUNT= "select a.* from  tb_hunter a "
			+ " where EXISTS(select * from tb_hunter_desc b where a.id=b.hunterId and a.binded=1)";
	
	public List<Hunter> getHunterList(int offset, int pageSize){
		return BaseDAO.queryForList(Hunter.class, SQL_GET_LIST,offset,pageSize);
	}

	public int getTotalNumForHunterList() {
		List<Hunter> list = BaseDAO.queryForList(Hunter.class,SQL_GET_LIST_COUNT);
		if(list==null){
			return 0;
		}
		return list.size();
	}
}
